﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Text;
using Model;

/// <summary>
/// ArchiveInformationService 的摘要说明
/// </summary>
/// 
namespace DA
{

    public class ArchiveInformationService
    {
        string sql = string.Empty;
        public ArchiveInformationService()
        {

        }
        //通过档案编号获取档案信息实体对象
        public ArchiveInformation GetArchiveInformation(string  fileNumber) 
        {
           ArchiveInformation archiveInformations = new ArchiveInformation();
            sql = "select * from ArchiveInformation where FileNumber="+"'"+fileNumber+"'";
            
            using (SqlDataReader dr = DB.DBHelper.Exc(sql))
            {
                if(dr.Read())
                {
                    ArchiveInformation archiveInformation = new ArchiveInformation();
                    archiveInformation.Student.StudentID = Convert.ToString(dr["StudentID"]);
                    archiveInformation.Address = Convert.ToString(dr["Address"]);
                    archiveInformation.TransferTime = Convert.ToDateTime(dr["TransferTime"]);
                    archiveInformation.ActivityAwardInformation[0].AwardID = Convert.ToString(dr["AwardID"]);
                    archiveInformation.ArchiveBorrowRecord[0].BorrowID = Convert.ToString(dr["BorrowID"]);
                }
            }
            return archiveInformations;
        }

        //通过学号获取档案编号
        public string GetFileNumberByStudnetID(string studentID)
        {
            string fileNumber = string.Empty;
            sql= "select FileNumber from ArchiveInformation where StudentID="+"'"+studentID+"'";
            SqlDataReader dr = DB.DBHelper.Exc(sql);
            if (dr.Read())
            {
                fileNumber = Convert.ToString(dr["FileNumber"]);
            }
            return fileNumber;
        }

        //通过档案编号获取学号
        public string   GetStudnetIDByFileNumber(string   filenumber)
        {
            string studentID = string .Empty;
            sql = "select StudentID from ArchiveInformation where FileNumber=" + "'" + filenumber + "'";
            SqlDataReader dr = DB.DBHelper.Exc(sql);
            if (dr.Read())
            {
                studentID= Convert.ToString(dr["StudentID"]);
            }

            return studentID;
        }

        
        //通过学号获取档案信息
        public IList<ArchiveInformation> GetArchiveInformationByStudentID(string studentID)
        {
            IList<ArchiveInformation> archiveInformations = new List<ArchiveInformation>();
            sql = "select * from ArchiveInformation where Student = " + studentID.ToString();
            
            using (SqlDataReader dr = DB.DBHelper.Exc(sql))
            {
                while (dr.Read())
                {
                    ArchiveInformation archiveInformation = new ArchiveInformation();
                    archiveInformation.FileNumber = Convert.ToString(dr["FileNumber"]);
                    archiveInformation.Address = Convert.ToString(dr["Address"]);
                    archiveInformation.TransferTime = Convert.ToDateTime(dr["TransferTime"]);
                    //archiveInformation.Student.StudentID = Convert.ToString(dr["StudentID"]);
                    archiveInformation.ArchiveBorrowRecord[0].BorrowID = Convert.ToString(dr["BorrowID"]);
                    archiveInformation.ActivityAwardInformation[0].AwardID = Convert.ToString(dr["AwardID"]);
                    archiveInformations.Add(archiveInformation);
                }
            }
            return archiveInformations;
        }

        //获取档案信息
        public IList<ArchiveInformation> GetArchiveInformation()  
        {
            IList<ArchiveInformation> archiveInformations = new List<ArchiveInformation>();
            sql = "select * from ArchiveInformation";

            using (DataSet ds = DB.DBHelper.Exc2(sql))
            {
                string studentId = string.Empty;
               foreach(DataRow dr in ds.Tables[0].Rows)
                {
                    ArchiveInformation archiveInformation = new ArchiveInformation();

                    archiveInformation.FileNumber = Convert.ToString(dr["FileNumber"]);
                    studentId = Convert.ToString(dr["StudentID"]);
                    //archiveInformation.ArchiveBorrowRecord[0].BorrowID = Convert.ToString(dr["BorrowID"]);
                    //archiveInformation.ActivityAwardInformation[0].AwardID = Convert.ToString(dr["AwardID"]);
                    archiveInformation.Address = Convert.ToString(dr["Address"]);
                    archiveInformation.TransferTime = Convert.ToDateTime(dr["TransferTime"]);

                    string sql_selectStudent = "select * from Student where StudentID='" + studentId + "'";
                    System.IO.File.AppendAllText("d:\\logs.log", DateTime.Now.ToString() + "," + sql_selectStudent+Environment.NewLine);
                    using (DataSet dsStudent = DB.DBHelper.Exc2(sql_selectStudent))
                    {
                        string studentName = dsStudent.Tables[0].Rows[0]["StudentName"].ToString();
                        
                        Student student = new Student(studentId, studentName);
                        archiveInformation.Student = student;
                    }


                        archiveInformations.Add(archiveInformation);
                }
            }
            return archiveInformations;
        }

         //删除档案信息
        public void DeleteArchiveInformationByFileNumber(string  fileNumber)  
        {
            sql = "delete from ArchiveInformation  where FileNumber=" + Convert.ToString(fileNumber);
            DB.DBHelper.ExecuteCommand(sql);
        }

         //修改档案信息
        public int  ModifyArchiveInformation(ArchiveInformation archiveInformation)  
        {
            int id = 0;
            StringBuilder updateArchiveInformation = new StringBuilder();
            updateArchiveInformation.Append("update ArchiveInformation set Address='" + archiveInformation.Address + "',TransferTime='" + archiveInformation.TransferTime
                 + "',AwardID='"+archiveInformation.ActivityAwardInformation+"',BorrowID='"+archiveInformation.ArchiveBorrowRecord
                 + "'where FileNumber=" + archiveInformation.FileNumber);
            sql = updateArchiveInformation.ToString();
            id=DB.DBHelper.ExecuteCommand(sql);
            return id;
        }

        //增加档案信息
        public int   AddArchiveInformation(ArchiveInformation archiveInformation)  
        {
            int id=0 ;
            StringBuilder addArchiveInformation = new StringBuilder();
            addArchiveInformation.Append(
                "insert into ArchiveInformation(FileNumber,StudentID, Address,TransferTime) values('"
                + archiveInformation.FileNumber
                +"','"+archiveInformation.Student.StudentID
               // +"','"+archiveInformation.ArchiveBorrowRecord
               // +"','"+archiveInformation.ActivityAwardInformation
                +"','"+archiveInformation.Address+"','" 
                + archiveInformation.TransferTime+ "');");
            sql = addArchiveInformation.ToString();
            System.IO.File.AppendAllText("d:\\logs.log", DateTime.Now.ToString() + sql + Environment.NewLine);
            id = DB.DBHelper.ExecuteCommand(sql);
            

            return id;
        }


    }
}